module.exports = class extends think.Model {
  detailSimple(startDate, endDate) {
    const queryStr = 'SELECT tm_build_team.phone_number,tm_build_team.id,MONTH(tm_build_team.create_time) as mon,DAY(tm_build_team.create_time) as day' +
      ',tm_build_team.guide_name,tm_build_team.team_name,tm_build_team.team_number' +
      ',tm_team_nature_one.NAME nature_one,tm_team_nature_two.NAME nature_two' +
      ',tm_team_travel.NAME team_travel,tm_build_team.tourist_view,TIME(tm_build_team.create_time) as start_time ' +
      'FROM tm_build_team , tm_team_nature_one ,tm_team_nature_two ,tm_team_travel ' +
      'WHERE tm_build_team.team_nature_one = tm_team_nature_one.type_code ' +
      'and tm_build_team.team_nature_two = tm_team_nature_two.type_code ' +
      'and tm_build_team.team_travel = tm_team_travel.type_code ' +
      'and tm_build_team.create_time > "' + startDate + ' 00:00:00" ' +
      'and tm_build_team.create_time < "' + endDate + '  23:59:59" ' +
      'ORDER BY tm_build_team.create_time;';
    return this.query(queryStr);
  }

  detailAttend(id) {
    const queryStr = 'SELECT TIME(tm_attend_record.attend_time) as attend_time,tm_tourist_attraction.`name`,tm_attend_record.tourist_attraction type ,tm_tourist_attraction.status,' +
      'tm_attend_record.status restatus ' +
      'FROM tm_build_team,tm_attend_record,tm_tourist_attraction ' +
      'WHERE tm_build_team.id = tm_attend_record.build_id ' +
      'and tm_build_team.id =' + id +
      ' and tm_attend_record.tourist_attraction =  tm_tourist_attraction.type_code ;';
    return this.query(queryStr);
  }

  personSimple(startDate, endDate) {
    const queryStr = 'SELECT tm_build_team.guide_name,COUNT(tm_build_team.guide_name) count\n' +
      'FROM tm_build_team\n' +
      'WHERE tm_build_team.create_time > "' + startDate + ' 00:00:00" ' +
      'and tm_build_team.create_time < "' + endDate + '  23:59:59" ' +
      'GROUP BY tm_build_team.guide_name';
    return this.query(queryStr);
  }

  travelSimple(startDate, endDate) {
    const queryStr = 'SELECT tm_build_team.team_name,COUNT(tm_build_team.team_name) count\n' +
      '      FROM tm_build_team' +
      ' WHERE tm_build_team.create_time > "' + startDate + ' 00:00:00" ' +
      ' and tm_build_team.create_time < "' + endDate + '  23:59:59" ' +
      ' GROUP BY tm_build_team.team_name';
    return this.query(queryStr);
  }

  personAttend(guideName, startDate, endDate) {
    const queryStr = 'SELECT tm_attend_record.tourist_attraction type,COUNT(tm_attend_record.tourist_attraction) count\n' +
      'FROM tm_attend_record\n' +
      'WHERE tm_attend_record.guide_name = "' + guideName + '" ' +
      'and tm_attend_record.attend_time > "' + startDate + ' 00:00:00" ' +
      'and tm_attend_record.attend_time < "' + endDate + '  23:59:59" ' +
      'GROUP BY tm_attend_record.tourist_attraction';
    return this.query(queryStr);
  }

  travelAttend(teamnName, startDate, endDate) {
    const queryStr =
      ' SELECT t.tourist_attraction type,COUNT( t.tourist_attraction ) count FROM(\n' +
      ' SELECT tar.build_id,tar.tourist_attraction,tar.guide_name,tbl.team_name,tar.attend_time \n' +
      ' FROM tm_attend_record tar LEFT JOIN tm_build_team tbl ON tar.build_id = tbl.id \n' +
      ' where tbl.team_name = "' + teamnName + '" ' +
      ' and tar.attend_time > "' + startDate + ' 00:00:00" ' +
      ' and tar.attend_time < "' + endDate + '  23:59:59" ' +
      ' order by  tourist_attraction) t GROUP BY t.tourist_attraction';

    return this.query(queryStr);
  }

  departmentSimple(startDate, endDate) {
    const queryStr = 'SELECT COUNT(tm_build_team.id) count\n' +
      'FROM tm_build_team ' +
      'WHERE tm_build_team.create_time > "' + startDate + ' 00:00:00" ' +
      'and tm_build_team.create_time < "' + endDate + '  23:59:59" ';
    return this.query(queryStr);
  }

  departmentAttend(startDate, endDate) {
    const queryStr = 'SELECT tm_attend_record.tourist_attraction type,COUNT(tm_attend_record.tourist_attraction) count\n' +
      'FROM tm_attend_record\n' +
      'WHERE tm_attend_record.attend_time > "' + startDate + ' 00:00:00" ' +
      'and tm_attend_record.attend_time < "' + endDate + '  23:59:59" ' +
      'GROUP BY tm_attend_record.tourist_attraction';
    return this.query(queryStr);
  }
};
